﻿/*
' DotNetNuke® - http://www.dotnetnuke.com
' Copyright (c) 2002-2009
' by DotNetNuke Corporation
'
' Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated 
' documentation files (the "Software"), to deal in the Software without restriction, including without limitation 
' the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and 
' to permit persons to whom the Software is furnished to do so, subject to the following conditions:
'
' The above copyright notice and this permission notice shall be included in all copies or substantial portions 
' of the Software.
'
' THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED 
' TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL 
' THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF 
' CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER 
' DEALINGS IN THE SOFTWARE.
*/

using System;
using System.Collections.Specialized;
using System.IO;
using System.Threading;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Gallio.Framework;
using DotNetNuke.Tests.UI.WatiN.Common;
using System.Data.SqlClient;

namespace DotNetNuke.Tests.UI.WatiN.Utilities
{
    public static class DatabaseManager
    {
        #region Private Methods

        private static void AddLogin(string login, string password)
        {
            // Connect to the SQL Server
            Server server = new Server(TestEnvironment.DatabaseServer);

            //Get the Login
            Login dbLogin = server.Logins[login];
            if (dbLogin == null)
            {
                //Create a new Login
                dbLogin = new Login(server, login);
                dbLogin.LoginType = LoginType.SqlLogin;
                dbLogin.PasswordPolicyEnforced = false;

                dbLogin.Create(password, LoginCreateOptions.None);
            }
        }

        private static string CopyDatabase(TestContext context, string sourceDatabasePath, string databaseName)
        {
            // Get the fileName (create the folder if neccessary)
            string databasePath = GetDataBaseFileName(context, databaseName, ".mdf");
            sourceDatabasePath = String.Format("{0}.mdf", Path.Combine(sourceDatabasePath, databaseName));

            // Copy the database to the test database directory
            File.Copy(sourceDatabasePath, databasePath);
            return databasePath;
        }

        private static void DropDatabase(Server server, string databaseName)
        {
            // Drop the database
            Database db = server.Databases[databaseName];
            if (db != null)
            {
                server.KillDatabase(databaseName);
                while (server.Databases[databaseName] != null)
                {
                    Thread.Sleep(100);
                }
            }
        }

     
        #endregion

        #region Public Methods

        public static void AddUser(string databaseName, string login, string password, string[] roles)
        {
            // Connect to the SQL Server
            Server server = new Server(TestEnvironment.DatabaseServer);

            //Get Database
            Database db = server.Databases[databaseName];

            //Make sure Login exists
            AddLogin(login, password);

            //Create a User for the Login
            User dbUser = new User(db, login);
            dbUser.Login = login;
            dbUser.Create();
            foreach (string role in roles)
            {
                dbUser.AddToRole(role);
            }
        }

        public static String GetDataBaseOwnerName(string databaseName)
        {
            // Connect to the SQL Server
            Server server = new Server(TestEnvironment.DatabaseServer);

            //Get Database
            Database db = server.Databases[databaseName];

            //Return the owner of the DB 
            return db.Owner;
        }

            
        public static void AttachDatabase(string databaseName, string databaseFile)
        {
            // Connect to the SQL Server
           // ServerConnection connection = new ServerConnection(TestEnvironment.DatabaseServer, TestUsers.DBUser.UserName, TestUsers.DBUser.Password);
            Server server = new Server(TestEnvironment.DatabaseServer);
            
            
            // Attach the database
            server.AttachDatabase(databaseName, new StringCollection()
            {
                databaseFile
            });
            while (server.Databases[databaseName].State != SqlSmoState.Existing)
            {
                Thread.Sleep(100);
            }

        }

        public static void SetDatabaseOwner(string DatabaseName, string ownerLogin)
        {
            //Create a sqlConnection
            SqlConnection connection = new SqlConnection(String.Format("Server={0};Database={1};Trusted_Connection=Yes",
                                                    TestEnvironment.DatabaseServer, DatabaseName));
            //Create a server connection
            ServerConnection sConnection = new ServerConnection(connection);

            //Connect to the server
            Server server = new Server(sConnection);

            //Locate the Database
            Database db = server.Databases[DatabaseName];


            //Change the owner of the DB
            db.SetOwner(ownerLogin, true);
        }

        public static void CopyAndAttachDatabase(TestContext context, string sourceDatabasePath, string databaseName)
        {
            string destinationPath = CopyDatabase(context, sourceDatabasePath, databaseName);

            // Attach the copied database to SQL Server
            AttachDatabase(databaseName, destinationPath);
        }

        public static void CreateDatabase(TestContext context, string databaseName)
        {
            // Connect to the SQL Server
            //ServerConnection connection = new ServerConnection(TestEnvironment.DatabaseServer, TestUsers.DBUser.UserName, TestUsers.DBUser.Password);
            Server server = new Server(TestEnvironment.DatabaseServer);

            // Drop the database
            DropDatabase(server, databaseName);

            //Instantiate a database object
            Database db = new Database(server, databaseName);

            //Create a PRIMARY FileGroup
            FileGroup fileGroup = new FileGroup(db, "PRIMARY");
            db.FileGroups.Add(fileGroup);

            //Create Primary Data File
            DataFile dbFile = new DataFile(fileGroup, databaseName, GetDataBaseFileName(context, databaseName, ".mdf"));
            dbFile.Size = (double)(5.0 * 1024.0);
            dbFile.GrowthType = FileGrowthType.Percent;
            dbFile.Growth = 25.0;
            dbFile.IsPrimaryFile = true;
            fileGroup.Files.Add(dbFile);

            //Create Log File
            string logFileName = String.Format("{0}_Log", databaseName);
            LogFile logFile = new LogFile(db, logFileName, GetDataBaseFileName(context, logFileName, ".ldf"));
            logFile.Size = (double)(5.0 * 1024.0);
            logFile.GrowthType = FileGrowthType.Percent;
            logFile.Growth = 25.0;
            db.LogFiles.Add(logFile);

            //Create Database
            db.Create(false);
        }

        public static void DetachAndCopyDatabase(string databaseName, string destinationPath, string destinationFileName)
        {
            // Connect to the SQL Server
            //ServerConnection sConnection = new ServerConnection(TestEnvironment.DatabaseServer, TestUsers.DBUser.UserName, TestUsers.DBUser.Password);
            Server server = new Server(TestEnvironment.DatabaseServer);
            //Server server = new Server(sConnection);

            //Get the database object
            Database db = server.Databases[databaseName];
            Database master = server.Databases["Master"];

            
            if (db != null)
            {
                //Get the file group
                FileGroup fileGroup = db.FileGroups["PRIMARY"];

                //Get the file
                DataFile dbFile = fileGroup.Files[0];
                string fileName = dbFile.FileName;

                //Detach Database
                server.KillAllProcesses(db.Name);
                master.ExecuteNonQuery("alter database " + databaseName + " set restricted_user with No_Wait;");
                db.DatabaseOptions.UserAccess = DatabaseUserAccess.Single;
                db.Alter(TerminationClause.RollbackTransactionsImmediately);
                server.DetachDatabase(db.Name, false, false);

                //Copy file(s) to destination
                if (!Directory.Exists(destinationPath))
                {
                    Directory.CreateDirectory(destinationPath);
                }
                string destinationDatabasePath = Path.Combine(destinationPath, destinationFileName) + ".mdf";
                File.Copy(fileName, destinationDatabasePath, true);
            }
        }

        public static void DropDatabase(string databaseName)
        {
            // Connect to the SQL Server
            //ServerConnection connection = new ServerConnection(TestEnvironment.DatabaseServer, TestUsers.DBUser.UserName, TestUsers.DBUser.Password);       
            Server server = new Server(TestEnvironment.DatabaseServer);

            // Drop the database
            DropDatabase(server, databaseName);
            
        }

    

        public static string GetDataBaseFileName(TestContext context, string databaseName, string extension)
        {
            string testDatabaseDirectory = Path.GetFullPath(String.Format(@"{0}\{1}",
                                                                TestEnvironment.DatabasePath,
                                                                context.Test.FullName));
            if (!Directory.Exists(testDatabaseDirectory))
            {
                Directory.CreateDirectory(testDatabaseDirectory);
            }

            string destinationRoot = Path.Combine(testDatabaseDirectory, databaseName);
            string databasePath = String.Concat(destinationRoot, extension);

            return databasePath;
        }

        public static void ExecuteSQLUpdateStringOnDatabase(string databaseName, string dbUserName, string dbUserPassword, string sqlString)
        {
            //Create the connection string
            string connectionString = String.Format("Server={0};Database={1};uid={2};pwd={3};",
                                                    TestEnvironment.DatabaseServer, databaseName, dbUserName, dbUserPassword);
            //Create connection object and open the connection
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();

            //Execute the SQL statement
            SqlCommand command = new SqlCommand(sqlString, connection);
            int rowsAffected = command.ExecuteNonQuery();

            //Close the connection
            connection.Close();
            if (rowsAffected == 0)
            {
                throw new System.Exception("No items in the Database were updated");
            }

        }

        

        #endregion

        
    }
}
